﻿using MySql.Data.MySqlClient;
using MySql.Data;
using System;
using System.IO;
using System.Data;
using System.Web;
using MySQLDriverCS;
using System.Text;

namespace SAC.mySqlHelper
{
    /// <summary>
    ///本类是IJSP为MAOGAME编写的ASP.NET 2.0 /3.5 + MYSQL 的数据库类
    /// </summary>
    public sealed class MySql
    {
        private static string msg = "";//用于记录数据库操作异常

        //pooling=false;

        string str1 = String.Format("server={0};userid={1}; password={2}; database={3}; port=3306", "10.138.32.101", "sis", "2027", "短信");
        // <add key="ConnectionString" value="Database=数据库名;Password=密码;User ID=用户名;Data Source=192.168.100.56;pooling=false;CharSet=utf8;port=3306"/>
        private static string dbconn = String.Format("server={0};userid={1}; password={2}; database={3}; port=3306;pooling=false; CharSet=utf8", "10.138.32.101", "sis", "2027", "短信");



        /// <summary>
        /// 链接mySql数据库
        /// </summary>
        private static string MySqlLink
        {
            get
            {
                return String.Format("server={0};userid={1}; password={2}; database={3}; port=3306", "10.138.32.101", "sis", "2027", "短信");
            }
        }

        //StreamReader sr = new StreamReader(DirectoryTools.MySqlLink, System.Text.Encoding.GetEncoding("utf-8"));


        // Encoding.GetEncoding("GB2312").GetString(buffer)
        //这里的DbUrl是与本类在同一文件夹下的一类，做用是提供一个数据库连接字符串，极为简单，不做说明

        // private static string dbconn = DbUrl.getLocal;

        //  private static MySqlConnection conn = new MySqlConnection(dbconn);//创建MYSQL连接
        //      System.Text.Encoding iso8859,gb2312;
        ////iso8859
        //iso8859    = System.Text.Encoding.GetEncoding("iso8859-1");
        ////国标2312
        //gb2312 = System.Text.Encoding.GetEncoding("gb2312");
        //byte[] bt;
        //bt = iso8859.GetBytes(xml);
        //xml = gb2312.GetString(bt);

        ///<summary>
        ///返回数据库操作异常
        ///</summary>
        ///<returns></returns>
        #region

        public static string getmsg()
        { return msg; }

        public static void setmsg(string s)
        {
            msg = s;
        }

        #endregion




        /// <summary>
        /// 关闭数据库连接，兼容老代码。。。
        /// </summary>
        #region
        public static void newClose()
        {

        }
        #endregion

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        #region
        public static void Close()
        {
            //try
            //{
            //    if (conn.State == ConnectionState.Open)
            //    {
            //        conn.Close();
            //        //conn.Dispose();
            //    }
            //}
            //catch (Exception cnss)
            //{
            //    msg = Convert.ToString(cnss.Message);
            //}
        }
        #endregion


        /// <summary>
        /// 打开数据库连接
        /// </summary>
        #region
        private static void Open()
        {
            //try
            //{
            //    if (conn == null)
            //    {
            //        conn = new MySqlConnection(dbconn);
            //        conn.Open();
            //    }
            //}
            //catch (MySqlException cn1)
            //{
            //    msg = Convert.ToString(cn1.Message);
            //    Close();//万一要是出现未知错误，打开失败或其它，这里再次确定关闭连接
            //}

            //try
            //{
            //    if (conn.State == ConnectionState.Closed)
            //    {
            //        conn.Open();
            //    }

            //    //if (conn.State != ConnectionState.Closed && conn.State != ConnectionState.Open)
            //    //{
            //    //    conn.Dispose();
            //    //}
            //}
            //catch (MySqlException cn)
            //{
            //    msg = Convert.ToString(cn.Message);
            //    Close();//万一要是出现未知错误，打开失败或其它，这里再次确定关闭连接
            //}

        }
        #endregion

        /// <summary>
        /// 获得MySqlDataReader对象 使用完须关闭数据库连接
        /// </summary>
        /// <param name="sql">执行普通sql查询语句</param>
        /// <returns></returns>
        #region
        public static MySqlDataReader ReturnDateReader(string sql)
        {


            try
            {
                return MySqlHelper.ExecuteReader(dbconn, sql);
            }
            catch (MySqlException rdr)
            {
                msg = Convert.ToString(rdr.Message);
                return null;
            }
            //finally
            //{
            //    Close();
            //}

        }
        #endregion


        /// <summary>
        /// 获得MySqlDataReader对象带参数 使用完须关闭数据库连接
        /// </summary>
        /// <param name="sql">执行普通sql查询语句</param>
        /// <returns></returns>
        #region
        public static MySqlDataReader ReturnDateReader(string sql, MySqlParameter[] par)
        {


            try
            {
                return MySqlHelper.ExecuteReader(dbconn, sql, par);
            }
            catch (MySqlException rdr)
            {
                msg = Convert.ToString(rdr.Message);
                return null;
            }
            //finally
            //{
            //    Close();
            //}

        }
        #endregion


        /// <summary>
        /// ReturnExeNoQuery语句，返回一个整数
        /// </summary>
        /// <param name="sql">执行Sql 更新，删除，插入等返回结果是整数</param>
        #region
        public static int ReturnExeNoQuery(string sql)
        {
            System.Text.Encoding iso8859, gb2312;
            //iso8859
            iso8859 = System.Text.Encoding.GetEncoding("iso8859-1");
            //国标2312
            gb2312 = System.Text.Encoding.GetEncoding("gb2312");
            byte[] bt;

            bt = iso8859.GetBytes(dbconn);
            dbconn = gb2312.GetString(bt);

            MySqlConnection conn = new MySqlConnection(dbconn);

            MySqlCommand commn = new MySqlCommand("set names utf8", conn);

            try
            {
                return MySqlHelper.ExecuteNonQuery(conn, sql);

            }
            catch (MySqlException renq)
            {
                msg = Convert.ToString(renq.Message);
                return -1;
            }




        }
        #endregion


        /// <summary>
        /// ReturnExeNoQuery语句 带参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        #region
        public static int ReturnExeNoQuery(string sql, MySqlParameter[] par)
        {
            try
            {
                return MySqlHelper.ExecuteNonQuery(dbconn, sql, par);

            }
            catch (MySqlException renq)
            {
                msg = Convert.ToString(renq.Message);
                return -1;
            }
        }
        #endregion

        /// <summary>
        /// 获得MySqlDataSet对象 使用完无须关闭数据库连接
        /// </summary>
        /// <param name="sql">执行普通sql查询语句</param>
        /// <returns></returns>
        #region
        public static DataSet ReturnDateSet(string sql)
        {
            // kill();

            //DataSet ds = null;

            //System.Text.Encoding iso8859, gb2312;
            ////iso8859
            //iso8859 = System.Text.Encoding.GetEncoding("iso8859-1");
            ////国标2312
            //gb2312 = System.Text.Encoding.GetEncoding("gb2312");
            //byte[] bt;

            //bt = iso8859.GetBytes(dbconn);
            //dbconn = gb2312.GetString(bt);

            MySqlConnection conn = new MySqlConnection(dbconn);

            MySqlCommand commn = new MySqlCommand("set names gb2312", conn);

            try
            {
                return MySqlHelper.ExecuteDataset(conn, sql);
                //return ds;

            }
            catch (MySqlException rds)
            {
                msg = Convert.ToString(rds.Message);
                return null;
            }
            //finally
            //{
            //    Close();
            //}

        }
        #endregion

        /// <summary>
        /// 获得MySqlDataSet对象,带参数。 使用完无须关闭数据库连接
        /// </summary>
        /// <param name="sql">sql语句，带参数</param>
        /// <param name="par">对应参数</param>
        /// <returns></returns>
        #region
        public static DataSet ReturnDateSet(string sql, MySqlParameter[] par)
        {
            try
            {
                return MySqlHelper.ExecuteDataset(dbconn, sql, par);
            }
            catch (MySqlException rds)
            {
                msg = Convert.ToString(rds.Message);
                return null;
            }

        }
        #endregion

        /// <summary>
        /// 获得MySqlDataScalar对象(返回一个字符串) 使用完无须关闭数据库连接
        /// </summary>
        /// <param name="sql">执行普通sql查询语句</param>
        /// <returns></returns>
        #region
        public static string ReturnScalar(string sql)
        {
            try
            {

                return Convert.ToString(MySqlHelper.ExecuteScalar(dbconn, sql));

            }
            catch (MySqlException rds)
            {
                msg = Convert.ToString(rds.Message);

                return "-1";
            }
            //finally
            //{
            //    Close();
            //}

        }
        #endregion

        /// <summary>
        /// 返回第一行第一列的值，类型为字符串
        /// </summary>
        /// <param name="sql">带参数的SQL</param>
        /// <param name="par">参数数组</param>
        /// <returns></returns>
        #region
        public static string ReturnScalar(string sql, MySqlParameter[] par)
        {
            try
            {
                return Convert.ToString(MySqlHelper.ExecuteScalar(dbconn, sql, par));
            }
            catch (MySqlException rds)
            {
                msg = Convert.ToString(rds.Message);

                return "-1";
            }
            //finally
            //{
            //    Close();
            //}

        }
        #endregion



        /// <summary>
        /// KILL　Mysql 线程，这个方法的目的是KILL掉长时间空闭的MYSQL线程，适合那些自己写MYSQL连接的新手朋友
        /// </summary>
        #region

        private static System.DateTime fristTime = System.DateTime.Now;

        public static void kill()
        {

            System.DateTime endTime = System.DateTime.Now.AddSeconds(-600);//每二十分钟执行一次KILL

            System.TimeSpan overTime = endTime - fristTime;

            Int16 verTime = Convert.ToInt16(overTime.Seconds);

            if (verTime > 0)
            {
                fristTime = System.DateTime.Now;

                //msg = "超过 " + Convert.ToString(verTime) + "秒";
                //msg += "<BR>第一次时间:" + Convert.ToString(fristTime);
                //msg += "<BR>本次时间:" + Convert.ToString( System.DateTime.Now);


                try
                {


                    DataSet killid = MySqlHelper.ExecuteDataset(dbconn, "SHOW PROCESSLIST;");

                    if (killid != null && killid.Tables[0].Rows.Count > 0)
                    {
                        for (int i0 = 0; i0 < killid.Tables[0].Rows.Count; i0++)
                        {
                            if (Convert.ToInt16(killid.Tables[0].Rows[i0]["time"]) > 600)
                            {
                                MySqlHelper.ExecuteNonQuery(dbconn, "KILL " + Convert.ToString(killid.Tables[0].Rows[i0]["id"]) + ";");
                            }
                        }
                    }


                }
                catch (MySqlException mye0)
                {
                    msg = mye0.Message.ToString();
                }

            }
            else
            {
                //msg = "不足KILL时间";
                //msg += "<BR>第一次时间:" + Convert.ToString(fristTime);
                //msg += "<BR>本次时间:" + Convert.ToString(System.DateTime.Now);
            }
        }

        #endregion


        /// <summary>
        /// //提供此方法以便在ASPX页面中通过 ijsp.Mysqlbean.connStateget() 获得连接的状态,类型为string
        /// </summary>
        #region
        public static string connStateget()
        {
            string states = "未知";
            //try
            //{
            //    if (conn.State == ConnectionState.Closed) states = "conn closed";
            //    if (conn.State == ConnectionState.Open) states = "conn open";
            //}
            //catch (MySqlException cnst)
            //{
            //    msg = Convert.ToString(cnst.Message);
            //}
            return states;

        }
        #endregion

    }

    public class MySql1
    {
        public string errMsg = "";

        public void test()
        {
            //String.Format("server={0};userid={1}; password={2}; database={3}; port=3306", "10.138.32.101", "sis", "2027", "短信");

            MySQLConnection conn = new MySQLConnection(new MySQLConnectionString("10.138.32.101", "duanxin", "sis", "2027").AsString);

            conn.Open();

            MySQLCommand commn = new MySQLCommand("set names gb2312", conn);

            commn.ExecuteNonQuery();

            string sql = "select * from 综合日报表_1 ";

            MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);

            DataSet ds = new DataSet();

            mda.Fill(ds, "table1");

            //this.dataGrid1.DataSource = ds.Tables["table1"];

            conn.Close();
        }


        public int RunCmd(string sql, out string errMsg)
        {
            int i = 0;

            MySQLConnection conn = new MySQLConnection(new MySQLConnectionString("10.138.32.101", "duanxin", "sis", "2027").AsString);

            MySQLCommand cmd = new MySQLCommand(sql, conn);

            errMsg = "";
            try
            {
                conn.Open();
                i = cmd.ExecuteNonQuery();
                return i;
            }
            catch (Exception ce)
            { errMsg = ce.Message; }
            finally
            { conn.Close(); }
            return i;
        }
    }
}


